package com.qq.financeSystem.mapper;

import com.qq.financeSystem.model.MoneyModel;
import com.qq.financeSystem.model.MonthGetMoney;
import com.qq.financeSystem.model.YearOutMoney;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.util.List;

@Mapper
@Repository
public interface AnalyzeMapper {

    //查询本月总收入（卖车）
    @Select("select IFNULL(SUM(selling_price),0)as sellcar_money \n" +
            "FROM order_info\n" +
            "where DATE_FORMAT(selling_time,'%Y%m') = DATE_FORMAT( CURDATE( ),'%Y%m')")
    public BigDecimal selectMonthCarMoney();

    //查询本月总收入（修理）
    @Select("select IFNULL(SUM(car_allmoney),0)as fixcar_money \n" +
            "FROM car_maintain\n" +
            "Where state = 0 and DATE_FORMAT(over_time,'%Y%m') = DATE_FORMAT( CURDATE( ),'%Y%m')")
    public BigDecimal selectMonthFixMoney();

    //查询本年度销售收入情况
    @Select("SELECT IFNULL(SUM(selling_price),0) FROM order_info WHERE YEAR(selling_time)=YEAR(NOW())")
    public BigDecimal selectYearCarMoney();

    //查询本年度销售数量情况
    @Select("SELECT COUNT(brand)as num FROM order_info WHERE YEAR(selling_time) = YEAR(NOW()) \n")
    public String selectYearCarCount();

    //查询本年度售后情况
    @Select("SELECT IFNULL(SUM(car_allmoney),0) FROM car_maintain WHERE state = 0 and YEAR(over_time)=YEAR(NOW())")
    public BigDecimal selectYearFixMoney();

    //查询累计服务客户
    @Select("SELECT SUM(num)as count\n" +
            "FROM(\n" +
            "select COUNT(car_number)as num FROM car_maintain WHERE state = 0 \n" +
            "UNION ALL\n" +
            "SELECT COUNT(brand)as num FROM order_info\n" +
            ")a")
    public String selectAllCourse();

    //查询本周每天车辆收入
    @Select("SELECT ifnull(b.sellcar_money,0)as money from (\n" +
            "    SELECT DATE(subdate(curdate(),date_format(curdate(),'%w')-1)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 1 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 2 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 3 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 4 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 5 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 6 day)) as click_date\n" +
            ") a LEFT JOIN(\n" +
            "  SELECT date(DATE_FORMAT(selling_time,'%Y%m%d')) as datetime,SUM(selling_price) as sellcar_money\n" +
            "  from order_info \n" +
            "  GROUP BY date(DATE_FORMAT(selling_time,'%Y%m%d'))\n" +
            "  )b on a.click_date = b.datetime")
    public List<String> selectWeekOneCarMoney();

    //查询本周每天售后收入
    @Select("SELECT ifnull(b.fixcar_money,0)as money from (\n" +
            "    SELECT DATE(subdate(curdate(),date_format(curdate(),'%w')-1)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 1 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 2 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 3 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 4 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 5 day)) as click_date\n" +
            "    union all\n" +
            "    SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 6 day)) as click_date\n" +
            ") a LEFT JOIN(\n" +
            "  SELECT date(DATE_FORMAT(over_time,'%Y%m%d')) as datetime,SUM(car_allmoney) as fixcar_money\n" +
            "  from car_maintain where state = 0\n" +
            "  GROUP BY date(DATE_FORMAT(over_time,'%Y%m%d'))\n" +
            "  )b on a.click_date = b.datetime")
    public List<String> selectWeekOneFixMoney();

    //查询近12月每月的（包含本月）车辆销售情况
    @Select("SELECT a.months as month,IFNULL(b.selling_price,0) as money from(\n" +
            "select DATE_FORMAT((CURDATE() - INTERVAL 0 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS months\n" +
            ") a LEFT JOIN(\n" +
            "SELECT SUM(selling_price)as selling_price,DATE_FORMAT(selling_time,'%Y-%m')as months " +
            "FROM order_info GROUP BY DATE_FORMAT(selling_time,'%Y-%m')" +
            ") b ON a.months = b.months ORDER BY a.months")
    public List<MoneyModel> selectTenTwoByOneCarMoney();

    //查询近12月每月的（包含本月）售后情况
    @Select("SELECT a.months as month,IFNULL(b.car_allmoney,0) as money from(\n" +
            "select DATE_FORMAT((CURDATE() - INTERVAL 0 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS months\n" +
            "UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS months\n" +
            ") a LEFT JOIN(\n" +
            "SELECT SUM(car_allmoney)as car_allmoney,DATE_FORMAT(over_time,'%Y-%m')as months FROM car_maintain " +
            "WHERE state=0 GROUP BY DATE_FORMAT(over_time,'%Y-%m')\n" +
            ") b ON a.months = b.months ORDER BY a.months")
    public List<MoneyModel> selectTenTwoByOneFixMoney();

    //查询本月总支出额（职员底薪+两个提成+补贴+进货车辆+零件）四舍五入取整
    @Select("SELECT ROUND(SUM(money))as money FROM(\n" +
            "SELECT IFNULL(SUM(salary),0)as money from employee_information\n" +
            "union ALL\n" +
            "SELECT IFNULL(SUM(royalty),0)as money from order_info where DATE_FORMAT(selling_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')\n" +
            "union ALL\n" +
            "SELECT IFNULL(SUM(push_money),0)as money from car_maintain where state = 0 and DATE_FORMAT(over_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')\n" +
            "union ALL\n" +
            "SELECT IFNULL(SUM(offset_money),0)as money from offset where DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')\n" +
            "union ALL\n" +
            "SELECT IFNULL(SUM(car_allprice),0)as money FROM car WHERE DATE_FORMAT(car_indate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')\n" +
            "union ALL\n" +
            "SELECT IFNULL(SUM(part_allprice),0)as money FROM part WHERE DATE_FORMAT(part_indate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')\n" +
            ")a")
    public String selectMonthAllOutMoney();

    //查询本年度总支出额（所有职员工资，仓库车辆+零件进货支出）
    @Select("SELECT SUM(money)as money FROM(\n" +
            "SELECT SUM(sum_money)as money from sendsalary where year(times) = year(NOW())\n" +
            "union ALL\n" +
            "SELECT SUM(car_allprice)as money FROM car WHERE year(car_indate) = year(NOW())\n" +
            "union ALL\n" +
            "SELECT SUM(part_allprice)as money FROM part WHERE year(part_indate) = year(NOW())\n" +
            ")a")
    public String selectYearAllOutMoney();

    //查询本年度汽车总量库存
    @Select("SELECT SUM(car_total) from car")
    public String selectYearAllCar();

    //查询所有在职人员数
    @Select("SELECT COUNT(employeeId) from employee_information")
    public String selectAllPerson();

    //本年度支出详情总览（1-12月）
    @Select("select a.mon as month,IFNULL(b.salary,0) as salary,IFNULL(c.salary,0) as carPrice,IFNULL(d.salary,0) as partPrice \n" +
            "FROM(\n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-01') as mon UNION ALL  \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-02') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-03') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-04') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-05') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-06') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-07') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-08') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-09') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-10') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-11') as mon UNION ALL \n" +
            "SELECT CONCAT(DATE_FORMAT(NOW(),'%Y'),'-12') as mon\n" +
            ")a\n" +
            "LEFT JOIN(\n" +
            "SELECT SUM(sum_money)as salary,DATE_FORMAT(times,'%Y-%m')as mont from sendsalary GROUP BY DATE_FORMAT(times,'%Y-%m')\n" +
            ")b on a.mon =b.mont\n" +
            "LEFT JOIN(\n" +
            "SELECT SUM(car_allprice)as salary,DATE_FORMAT(car_indate,'%Y-%m')as mont from car GROUP BY DATE_FORMAT(car_indate,'%Y-%m')\n" +
            ")c on a.mon = c.mont\n" +
            "LEFT JOIN(\n" +
            "SELECT SUM(part_allprice)as salary,DATE_FORMAT(part_indate,'%Y-%m')as mont from part GROUP BY DATE_FORMAT(part_indate,'%Y-%m')\n" +
            ")d\n" +
            " ON a.mon=d.mont")
    public List<YearOutMoney> selectYearAllOutMoneyShow();

    //查询本月销售详情订单
    @Select("SELECT salesman_id as id,salesman_name as name,brand,type,selling_price as price,selling_time as time " +
            "from order_info where DATE_FORMAT(selling_time,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m')")
    public List<MonthGetMoney> monthGetMoneyForCar();

    //查询本月维修详情订单
    @Select("SELECT job_num as id,person as name,car_type as brand,color as type,car_allmoney as price,over_time as time" +
            " from car_maintain where state = 0 and DATE_FORMAT(over_time,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m')")
    public List<MonthGetMoney> monthGetMoneyForFix();

}
